﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using SW.Lite.IDAL.Master;
using SW.Lite.Infrastructure.Common;
using SW.Lite.Infrastructure.Helpers;
using SW.Lite.Model.Master;

namespace SW.Lite.DAL.MsSql.Master
{
    public class Role : IRole
    {
        public bool Exists(int id)
        {
            const string sql = "SELECT COUNT(1) FROM SW_M_Role WHERE Id=@Id";
            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return MsSqlHelper.Exists(sql, paramter);
        }

        public RoleModel Single(int id)
        {
            const string sql = "SELECT Id, Code, Title FROM SW_M_Role WHERE Id=@Id";

            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return FillSingle(sql, paramter);
        }

        public PageList<RoleModel> All()
        {
            const string sql = "SELECT Id, Code, Title FROM SW_M_Role";

            return FillList(sql);
        }

        public PageList<RoleModel> All(int pageIndex, int pageSize)
        {
            var start = (pageIndex - 1) * pageSize;
            var end = pageIndex * pageSize;
            var sql =
                string.Format(@"SELECT Id, Code, Title FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) ROWNO,* FROM SW_M_Role) A WHERE A.ROWNO BETWEEN {0} AND {1};
            SELECT COUNT(1) FROM SW_M_Role;", start, end);
            return FillList(sql);
        }

        public ExecuteResult Insert(RoleModel model)
        {
            const string sql = "INSERT INTO SW_M_Role(Code, Title) VALUES (Code, @Title)";

            var parameters = new[]
            {
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Update(RoleModel model)
        {
            const string sql = "UPDATE SW_M_Role SET Code=@Code, Title=@Title WHERE Id=@Id ";
            var parameters = new[]
            {
                new SqlParameter("@Id", SqlDbType.Int) {Value = model.Id},
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Delete(params int[] ids)
        {
            if (ids == null || ids.Length <= 0)
                return new ExecuteResult { Result = false, Message = "删除失败" };

            var sql = "DELETE FROM SW_M_Role WHERE Id IN(" + String.Join(",", ids.Select(id => id.ToString())) + ")";
            var result = MsSqlHelper.Execute(sql);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public PageList<RoleModel> GetRolesByAdminId(int adminId)
        {
            const string sql =
                "SELECT Id, Code, Title FROM SW_M_Role WHERE Id IN(SELECT RoleId FROM SW_M_AdminRole WHERE AdminId=@Id)";

            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = adminId };

            return FillList(sql, paramter);
        }

        public PageList<RoleModel> GetRolesByCodes(params string[] codes)
        {
            if (codes == null || codes.Length <= 0)
                return new PageList<RoleModel>();

            var codesStr = String.Join("','", codes);

            var sql = "SELECT Id, Code, Title FROM SW_M_Role WHERE Code IN ('" + codesStr + "')";

            return FillList(sql);
        }

        public ExecuteResult SaveRoleMenus(int roleId, params int[] menuIds)
        {
            if (menuIds == null || menuIds.Length <= 0)
                return new ExecuteResult { Result = false, Message = "添加失败" };

            var sbBuilder = new StringBuilder("DELETE FROM SW_M_MenuRole WHERE RoleID=" + roleId);
            foreach (var item in menuIds)
                sbBuilder.AppendLine(String.Format("INSERT INTO SW_M_MenuRole(RoleID,MenuID)VALUES({0},{1});", roleId, item));

            var result = MsSqlHelper.Execute(sbBuilder.ToString());
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult SaveRoleButtons(int roleId, params int[] buttonIds)
        {
            if (buttonIds == null || buttonIds.Length <= 0)
                return new ExecuteResult { Result = false, Message = "添加失败" };

            var sbBuilder = new StringBuilder("DELETE FROM SW_M_ButtonRole WHERE RoleID=" + roleId);
            foreach (var item in buttonIds)
                sbBuilder.AppendLine(String.Format("INSERT INTO SW_M_ButtonRole(RoleID,ButtonID)VALUES({0},{1});", roleId, item));

            var result = MsSqlHelper.Execute(sbBuilder.ToString());
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        private RoleModel FillSingle(string sql, params SqlParameter[] paramters)
        {
            RoleModel data = null;
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                if (rdr.Read())
                    data = ReadData(rdr);
            }
            return data;
        }

        private PageList<RoleModel> FillList(string sql, params SqlParameter[] paramters)
        {
            var list = new PageList<RoleModel>();
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                while (rdr.Read())
                    list.Add(ReadData(rdr));
                if (rdr.NextResult() && rdr.Read())
                    list.AllCount = rdr.GetInt32(0);
                else
                    list.AllCount = list.Count;
            }
            return list;
        }

        private RoleModel ReadData(SqlDataReader dr)
        {
            return new RoleModel
            {
                Id = Convert.ToInt32(dr["Id"]),
                Code = Convert.ToString(dr["Code"]),
                Title = Convert.ToString(dr["Title"])
            };
        }
    }
}